package com.opennews.openplatform.service;

import com.opennews.openplatform.common.constant.IncomeExpenseTypeConstant;
import com.opennews.openplatform.common.constant.PaymentTypeConstant;
import com.opennews.openplatform.dto.income.expense.detail.IncomeExpenseDetailQueryArgument;
import com.opennews.openplatform.dto.journey.income.expense.detail.BaseJourneyIncomeExpenseDetailDto;
import com.opennews.openplatform.dto.journey.income.expense.detail.CreateJourneyIncomeExpenseDetailDto;
import com.opennews.openplatform.dto.journey.income.expense.detail.UpdateJourneyIncomeExpenseDetailDto;
import com.opennews.openplatform.entity.tables.records.JourneyIncomeExpenseDetailRecord;
import com.opennews.openplatform.jooq.DSLContextWrapper;
import com.opennews.openplatform.myspringbootcore.util.SharedUtil;
import jakarta.validation.Valid;
import lombok.RequiredArgsConstructor;
import org.jooq.Condition;
import org.jooq.DSLContext;
import org.jooq.impl.DSL;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.opennews.openplatform.entity.Tables.*;

@RequiredArgsConstructor
@Service
public class JourneyIncomeExpenseDetailService {
    private final DSLContext dslContext;
    private final DSLContextWrapper dslContextWrapper;
    private final BankService bankService;
    private final UserService userService;

    /**
     * Queries all journey income/expense which associated with provided account group.
     *
     * @return The list of map instance which contains keys below.
     * id: String of the income expense detail id.
     * location: String of the income or expense location.
     * currency: String of the income or expense currency.
     * amount: Float of the income expense detail amount.
     * type: String of the type. It's income or expense.
     * paymentType: String of the payment type which how the expense payed.
     * bankId: String of the band id of the bank if the expense is payed by card.
     * bankShortName: String of the short name of the bank if the expense is payed by card.
     * bankLogoUrl: String of the logo url of the bank if the expense is payed by card.
     * bankCardId: String of the id of the bank card if the expense is payed by card.
     * bankCardName: String of the name of the bank card if the expense is payed by card.
     * bankCardIsCreditCard: Bool of if this bank card is credit card.
     * details: String of the income or expense detail.
     */
    public List<Map<String, Object>> queryListByJourney(String journeyId, IncomeExpenseDetailQueryArgument queryArgument) {
        // Projections
        var select = dslContext.select(
                JOURNEY_INCOME_EXPENSE_DETAIL.ID.as("id"),
                JOURNEY_INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.as("incomeExpenseDate"),
                JOURNEY_INCOME_EXPENSE_DETAIL.LOCATION.as("location"),
                JOURNEY_INCOME_EXPENSE_DETAIL.CURRENCY.as("currency"),
                JOURNEY_INCOME_EXPENSE_DETAIL.AMOUNT.as("amount"),
                JOURNEY_INCOME_EXPENSE_DETAIL.TYPE.as("type"),
                JOURNEY_INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.as("paymentType"),
                BANK.ID.as("bankId"),
                BANK.SHORT_NAME.as("bankShortName"),
                BANK_CARD.ID.as("bankCardId"),
                BANK_CARD.NAME.as("bankCardName"),
                BANK_CARD.IS_CREDIT_CARD.as("bankCardIsCreditCard"),
                JOURNEY_INCOME_EXPENSE_DETAIL.DETAILS.as("details"),
                (!SharedUtil.isNullOrEmpty(queryArgument.keyword) ? JOURNEY.ID : DSL.val((String) null)).as("journeyId"),
                (!SharedUtil.isNullOrEmpty(queryArgument.keyword) ? JOURNEY.START_DATE : DSL.val((LocalDateTime) null)).as("journeyStartDate"),
                (!SharedUtil.isNullOrEmpty(queryArgument.keyword) ? JOURNEY.END_DATE : DSL.val((LocalDateTime) null)).as("journeyEndDate"),
                (!SharedUtil.isNullOrEmpty(queryArgument.keyword) ? REGION.TITLE : DSL.val((String) null)).as("regionTitle")
        );

        // Queries detail by account group id.
        var query = select.from(JOURNEY_INCOME_EXPENSE_DETAIL)
                .leftJoin(BANK_CARD).on(JOURNEY_INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(BANK_CARD.ID))
                .leftJoin(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID));

        if (!SharedUtil.isNullOrEmpty(queryArgument.keyword)) {
            // Sets the join type for journey property in order to get its region.
            // Sets the join type for region property in order to get its title.
            query.join(JOURNEY).on(JOURNEY_INCOME_EXPENSE_DETAIL.JOURNEY_ID.eq(JOURNEY.ID))
                    .join(REGION).on(JOURNEY.REGION_ID.eq(REGION.ID));
        }

        query.where(
                JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(queryArgument.accountGroupId)
        );

        if (!SharedUtil.isNullOrEmpty(journeyId)) {
            query.and(JOURNEY_INCOME_EXPENSE_DETAIL.JOURNEY_ID.eq(journeyId));
        }

        if (!SharedUtil.isNullOrEmpty(queryArgument.keyword)) {
            // When searching with AND condition for multiple keywords then we just need to wrap each keyword into one OR section.
            // The keyword supports separated by blank string.
            // When it occurs we are splitting the keyword with blank string and gets multiple keywords.
            // The multiple keywords will be used to search within OR condition.
            // Regarding the dynamic property in criteria please check on https://stackoverflow.com/questions/11550507/build-createcriteria-in-grails-dynamically-and-in-a-dry-way
            String[] keywords = queryArgument.keyword.split("\\s+");
            Condition keywordCondition = DSL.noCondition();

            for (String key : keywords) {
                keywordCondition = keywordCondition.and(JOURNEY_INCOME_EXPENSE_DETAIL.DETAILS.like("%" + key + "%"))
                        .or(JOURNEY_INCOME_EXPENSE_DETAIL.REMARK.like("%" + key + "%"));
            }

            query.and(keywordCondition);
        }

        query.orderBy(
                JOURNEY_INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.desc(),
                JOURNEY_INCOME_EXPENSE_DETAIL.TYPE.desc(),
                BANK_CARD.ID.asc(),
                JOURNEY_INCOME_EXPENSE_DETAIL.LAST_UPDATED.desc()
        );

        if (queryArgument.pageSize != null) {
            // How many rows are return for this trip.
            query.limit(queryArgument.pageSize);
        }

        if (queryArgument.pageSize != null && queryArgument.pageIndex != null) {
            // Where to take the rows.
            query.offset(queryArgument.pageIndex * queryArgument.pageSize);
        }

        return query.fetch()
                .map(p -> {
                    Map<String, Object> item = p.intoMap();

                    // Adds bankLogoUrl key to the map item.
                    item.put("bankLogoUrl", bankService.getLogoUrl((String) item.get("bankShortName")));

                    return item;
                });
    }

    /**
     * Queries journey income expense details by its id.
     *
     * @param accountGroupId: The string of user account group id.
     * @param id:             The string of the income expense detail id.
     * @return The map instance which contains keys below.
     * id: String of the income expense detail id.
     * journeyId: String of the journey id.
     * location: String of the income or expense location.
     * currency: String of the income or expense currency.
     * amount: Float of the income expense detail amount.
     * type: String of the type. It's income or expense.
     * paymentType: String of the payment type which how the expense payed.
     * bankId: String of the band id of the bank card if the expense is payed by card.
     * bankShortName: String of the short name of the bank card if the expense is payed by card.
     * bankCardId: String of the id of the bank card if the expense is payed by card.
     * bankCardName: String of the name of the bank card if the expense is payed by card.
     * details: String of the income or expense detail.
     * remark: String of the necessary extra remark.
     * createdBy: String of username of who created this item.
     * updatedBy: String of username of who updated this item.
     * dateCreated: Date of when this detail created.
     * lastUpdated: Date of when this detail updated.
     */
    public Map<String, Object> queryDetails(String accountGroupId, String id) {
        // Queries detail by account group id.
        Map<String, Object> result = dslContext.select(
                        JOURNEY_INCOME_EXPENSE_DETAIL.ID.as("id"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.JOURNEY_ID.as("journeyId"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE.as("incomeExpenseDate"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.LOCATION.as("location"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.CURRENCY.as("currency"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.AMOUNT.as("amount"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.TYPE.as("type"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.PAYMENT_TYPE.as("paymentType"),
                        BANK.ID.as("bankId"),
                        BANK.SHORT_NAME.as("bankShortName"),
                        BANK_CARD.ID.as("bankCardId"),
                        BANK_CARD.NAME.as("bankCardName"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.DETAILS.as("details"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.REMARK.as("remark"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.LATITUDE.as("latitude"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.LONGITUDE.as("longitude"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.ADDRESS.as("address"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.as("formattedAddress"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.PROVINCE.as("province"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.CITY.as("city"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.DISTRICT.as("district"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.CREATED_BY.as("createdBy"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.UPDATED_BY.as("updatedBy"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.DATE_CREATED.as("dateCreated"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.LAST_UPDATED.as("lastUpdated")
                )
                .from(JOURNEY_INCOME_EXPENSE_DETAIL)
                .leftJoin(BANK_CARD).on(JOURNEY_INCOME_EXPENSE_DETAIL.BANK_CARD_ID.eq(BANK_CARD.ID))
                .leftJoin(BANK).on(BANK_CARD.BANK_ID.eq(BANK.ID))
                .where(
                        JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                .and(JOURNEY_INCOME_EXPENSE_DETAIL.ID.eq(id))
                )
                .fetchOneMap();

        if (result == null) {
            return new HashMap<>();
        }

        // Queries createdBy & updatedBy basic info.
        List<Map<String, Object>> userDetails = userService.queryBasicInfo(accountGroupId, List.of(result.get("createdBy").toString(), result.get("updatedBy").toString()));
        result.put("createdBy", userDetails.stream().filter(p -> p.get("username").equals(result.get("createdBy"))).findFirst().get());
        result.put("updatedBy", userDetails.stream().filter(p -> p.get("username").equals(result.get("updatedBy"))).findFirst().get());

        // Sets the bank logo url based on its short name.
        result.put("bankLogoUrl", bankService.getLogoUrl((String) result.get("bankShortName")));

        return result;
    }

    /**
     * Queries all distinct income/expense years.
     *
     * @param accountGroupId: The string of the user account group id.
     * @return The list of Integer of existing year.
     */
    public List<Integer> queryExistingDistinctYears(String accountGroupId) {
        return dslContext.selectDistinct(DSL.year(JOURNEY_INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).as("year"))
                .from(JOURNEY_INCOME_EXPENSE_DETAIL)
                .where(JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                .orderBy(DSL.year(JOURNEY_INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).desc())
                .fetch()
                .getValues("year", Integer.class);
    }

    public List<Map<String, Object>> queryCoordinates(String accountGroupId, String year) {
        // Locations contain same formatted address but different coordinates which we like to take only one match.
        // https://www.navicat.com/en/company/aboutus/blog/1647-applying-select-distinct-to-one-column-only
        var subquery = dslContext.select(DSL.max(JOURNEY_INCOME_EXPENSE_DETAIL.ID))
                .from(JOURNEY_INCOME_EXPENSE_DETAIL)
                .where(
                        JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                                .and(JOURNEY_INCOME_EXPENSE_DETAIL.TYPE.eq(IncomeExpenseTypeConstant.EXPENSE))
                                .and(JOURNEY_INCOME_EXPENSE_DETAIL.LATITUDE.isNotNull())
                                .and(JOURNEY_INCOME_EXPENSE_DETAIL.LONGITUDE.isNotNull())
                                .and(JOURNEY_INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.isNotNull())
                                .and(JOURNEY_INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.ne(""))
                                .and(
                                        SharedUtil.isNullOrEmpty(year) ?
                                                DSL.trueCondition() :
                                                DSL.year(JOURNEY_INCOME_EXPENSE_DETAIL.INCOME_EXPENSE_DATE).eq(Integer.valueOf(year))
                                )
                )
                .groupBy(JOURNEY_INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS);

        return dslContext.select(
                        JOURNEY_INCOME_EXPENSE_DETAIL.FORMATTED_ADDRESS.as("formattedAddress"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.LATITUDE.as("latitude"),
                        JOURNEY_INCOME_EXPENSE_DETAIL.LONGITUDE.as("longitude")
                )
                .from(JOURNEY_INCOME_EXPENSE_DETAIL)
                .where(
                        JOURNEY_INCOME_EXPENSE_DETAIL.ID.in(subquery)
                )
                .fetchMaps();
    }

    /**
     * Adds a new record of JourneyIncomeExpenseDetail with posted data.
     * The username used to query the account group id and saved to createdBy and updatedBy.
     *
     * @param data:           The CreateJourneyIncomeExpenseDetailDto instance holds data.
     * @param accountGroupId: The string of user account group id.
     * @param username:       The string of username which is saved to createdBy and updatedBy.
     */
    public void add(String accountGroupId, String username, @Valid CreateJourneyIncomeExpenseDetailDto data) {
        // Creates new JourneyIncomeExpenseDetail.
        JourneyIncomeExpenseDetailRecord incomeExpenseDetail = createRecord(data, username);

        // Associates with account group.
        incomeExpenseDetail.setAccountGroupId(accountGroupId);

        // Associates with journey.
        incomeExpenseDetail.setJourneyId(data.getJourney().getId());

        // Specifies creator.
        incomeExpenseDetail.setCreatedBy(username);

        // Saves change.
        incomeExpenseDetail.insert();
    }

    /**
     * Updates existing JourneyIncomeExpenseDetail with posted data.
     *
     * @param accountGroupId: The string of user account group id.
     * @param username:       The string of username which saved to updatedBy.
     * @param data:           The map instance which contains the keys with same names as JourneyIncomeExpenseDetail properties.
     */
    public void update(String accountGroupId, String username, @Valid UpdateJourneyIncomeExpenseDetailDto data) {
        // Just makes sure only updates owned data.
        if (dslContextWrapper.count(JOURNEY_INCOME_EXPENSE_DETAIL, JOURNEY_INCOME_EXPENSE_DETAIL.ID.eq(data.getId())
                .and(JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))) == 1) {
            JourneyIncomeExpenseDetailRecord record = createRecord(data, username);
            record.changed(INCOME_EXPENSE_DETAIL.ID, false);

            // Specifies updater.
            record.setUpdatedBy(username);

            record.update();
        }
    }

    /**
     * Deletes the JourneyIncomeExpenseDetail with provides list of id.
     *
     * @param accountGroupId: The string of user account group id.
     * @param ids             The string list of id.
     */
    public void delete(String accountGroupId, List<String> ids) {
        if (!ids.isEmpty()) {
            // Executes batch deleting by DetachedCriteria.
            dslContext.deleteFrom(JOURNEY_INCOME_EXPENSE_DETAIL)
                    .where(
                            JOURNEY_INCOME_EXPENSE_DETAIL.ID.in(ids)
                                    .and(JOURNEY_INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId))
                    )
                    .execute();
        }
    }

    /**
     * Creates JourneyIncomeExpenseDetailRecord based on BaseJourneyIncomeExpenseDetailDto.
     *
     * @param data:     The map instance which contains the keys with same names as IncomeExpenseDetail properties.
     * @param username: The string of username which saved to updatedBy.
     */
    private JourneyIncomeExpenseDetailRecord createRecord(@Valid BaseJourneyIncomeExpenseDetailDto data, String username) {
        JourneyIncomeExpenseDetailRecord record = dslContext.newRecord(JOURNEY_INCOME_EXPENSE_DETAIL);

        // Updates the bank card property.
        record.setBankCardId(data.getBankCard() != null ? data.getBankCard().getId() : null);

        // Updates all properties which data contains.
        record.from(data);

        // Specifies updater.
        record.setUpdatedBy(username);

        // Sets the default payment type if needed.
        if (SharedUtil.isNullOrEmpty(record.getPaymentType())) {
            record.setPaymentType(PaymentTypeConstant.CASH);
        }

        // Just in case user inputs negative number for income and positive number for expense.
        // We take the absolute value first.
        record.setAmount(record.getAmount().abs());

        // If it is expense we take it as negative number.
        if (record.getType().equals(IncomeExpenseTypeConstant.EXPENSE)) {
            record.setAmount(record.getAmount().negate());
        }

        return record;
    }
}
